﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Wwtfly.BBS.Framework.Common;
using Wwtfly.BBS.BackModels.Models.News;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;
using Wwtfly.DBUtility;
using System.Data;
using System.Data.SqlClient;

namespace Wwtfly.BBS.BackDAL.News
{
    
    /// <summary>
    /// 新闻 DAL
    /// </summary>
    public class NewsDAL
    {


        /// <summary>
        /// 分页获取 新闻  sql语句
        /// </summary>
        private static readonly string GetNewsShowsInfoPageList_Select = "proc_GetNewsShowsInfoPageList_Select";

        /// <summary>
        /// 根据ID查询
        /// </summary>
        public const string PM_NEWSSHOWS_SELECT_BY_ID = " SELECT [Id],[TypeId],[Keyword],[NewsTitle],[NewsContent],[NewsInTime],[NewsClick],[NewsTag],[NewsState],[NewsRemark],[CreateTime],[CreateUser],[LastModifyTime],[LastModifyUser]" +
                                           " FROM [pm_newsShows]" +
                                           " WHERE (" +
                                           " [Id] = @Id)";

       
        /// <summary>
        /// 添加
        /// </summary>
        public const string PM_NEWSSHOWS_INSERT = " INSERT INTO [pm_newsShows] (" +
                                            "[TypeId],[Keyword],[NewsTitle],[NewsContent],[NewsInTime],[NewsClick],[NewsTag],[NewsState],[NewsRemark],[CreateTime],[CreateUser],[LastModifyTime],[LastModifyUser]" +
                                        ") VALUES (" +
                                            "@TypeId,@Keyword,@NewsTitle,@NewsContent,@NewsInTime,@NewsClick,@NewsTag,@NewsState,@NewsRemark,@CreateTime,@CreateUser,@LastModifyTime,@LastModifyUser" +
                                        ");select @Id = @@Identity;";

        /// <summary>
        /// 修改
        /// </summary>
        public const string PM_NEWSSHOWS_UPDATE = " UPDATE [pm_newsShows] SET " +
                                            "[TypeId]=@TypeId,[Keyword]=@Keyword,[NewsTitle]=@NewsTitle,[NewsContent]=@NewsContent,[NewsInTime]=@NewsInTime,[NewsClick]=@NewsClick,[NewsTag]=@NewsTag,[NewsState]=@NewsState,[NewsRemark]=@NewsRemark,[LastModifyTime]=@LastModifyTime,[LastModifyUser]=@LastModifyUser" +
                                        " WHERE " +
                                            " [Id] = @Id";


        /// <summary>
        /// 删除
        /// </summary>
        public const string PM_NEWSSHOWS_DELETE = "DELETE FROM [pm_newsShows]" +
                                        " WHERE " +
                                            " [Id] = @Id";

        /// <summary>
        /// 查询前几条新闻
        /// </summary>
        public const string PM_NEWSSHOWS_SelectSum = "proc_pm_newsShowsCount";

        public const string pm_newshows_selectContent = "proc_pm_newsShowsContentCount";
            /// <summary>
        ///  获取首页新闻列表
        /// </summary>
        /// <param name="sum">显示数据条数</param>
        /// <param name="TypeId">新闻类型ID</param>
        /// <returns></returns>
        public static DataTable NewsConetList(int sum, int TypeId)
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetStoredProcCommand(pm_newshows_selectContent);
            db.AddInParameter(dbCommand, "@sum", DbType.Int32, sum);
            db.AddInParameter(dbCommand, "@typeid", DbType.Int32, TypeId);
            DataSet ds = db.ExecuteDataSet(dbCommand);
            return ds == null ? null : ds.Tables[0];
        }
        
        /// <summary>
        ///  获取首页新闻列表
        /// </summary>
        /// <param name="sum">显示数据条数</param>
        /// <param name="TypeId">新闻类型ID</param>
        /// <returns></returns>
        public static DataTable NewsList(int sum, int TypeId)
        {
         
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetStoredProcCommand(PM_NEWSSHOWS_SelectSum);
            db.AddInParameter(dbCommand, "@sum", DbType.Int32, sum);
            db.AddInParameter(dbCommand, "@typeid", DbType.Int32, TypeId);
            DataSet ds = db.ExecuteDataSet(dbCommand);
            return ds == null ? null : ds.Tables[0];
        }
        /// <summary>
        /// 分页 获取新闻 的相关信息
        /// </summary>
        /// <param name="pageParams">分页参数</param>
        /// <param name="searchModel">新闻搜索 实体类</param>
        /// <returns>分页后的结果</returns>
        public static PagedList<NewsShowsModel>
            GetNewsShowsPageList(PageParams pageParams, NewsShowsSearchModel searchModel)
        {
            IList<NewsShowsModel> list = null;
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetStoredProcCommand(GetNewsShowsInfoPageList_Select);
            DBParamsUtility.CreatePageParamsOfNotOutTotalCount(pageParams, db, dbCommand);
            dbCommand.Parameters.Add(new SqlParameter() { ParameterName = "@NewsTitle", SqlDbType = SqlDbType.NVarChar, Value = searchModel.NewsTitle });
            dbCommand.Parameters.Add(new SqlParameter() { ParameterName = "@TypeId", SqlDbType = SqlDbType.Int, Value = searchModel.TypeId });
            using (IDataReader dr = db.ExecuteReader(dbCommand))
            {
                list = Emit.ConvertToList<NewsShowsModel>(dr);
                pageParams.SetTotalCount((int)DBParamsUtility.GetTotalCount<NewsShowsModel>(list));
            }
            return list.ToPagedList<NewsShowsModel>(pageParams);
        }

        /// <summary>
        /// 根据Id获取指定的pmnewsShows(新闻)
        /// </summary>
        /// <param name="Id">model主键</param>
        /// <returns></returns>
        public static NewsShowsModel GetInfo(Int32 id)
        {
            using (IDataReader dr = DBHelper.ExecuteReader(PM_NEWSSHOWS_SELECT_BY_ID,
                new SqlParameter() { ParameterName = "@Id", SqlDbType = SqlDbType.Int, Value = id }
                ))
            {
                return Emit.ConvertTo<NewsShowsModel>(dr);
            }
        }

        /// <summary>
        /// 添加新的pmnewsShows(新闻)
        /// </summary>
        /// <param name="model">pmnewsShows实体</param>
        /// <returns></returns>
        public static int Add(NewsShowsModel model)
        {
            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand = db.GetSqlStringCommand(PM_NEWSSHOWS_INSERT);

            db.AddOutParameter(dbCommand, "@Id", DbType.Int32, 4);
            db.AddInParameter(dbCommand, "@TypeId", DbType.Int32, model.TypeId);
            db.AddInParameter(dbCommand, "@Keyword", DbType.String, model.Keyword);
            db.AddInParameter(dbCommand, "@NewsTitle", DbType.String, model.NewsTitle);
            db.AddInParameter(dbCommand, "@NewsContent", DbType.String, model.NewsContent);
            db.AddInParameter(dbCommand, "@NewsInTime", DbType.DateTime, model.NewsInTime);
            db.AddInParameter(dbCommand, "@NewsClick", DbType.Int32, model.NewsClick);
            db.AddInParameter(dbCommand, "@NewsTag", DbType.String, model.NewsTag);
            db.AddInParameter(dbCommand, "@NewsState", DbType.Boolean, model.NewsState);
            db.AddInParameter(dbCommand, "@NewsRemark", DbType.String, model.NewsRemark);
            db.AddInParameter(dbCommand, "@CreateTime", DbType.DateTime, model.CreateTime);
            db.AddInParameter(dbCommand, "@CreateUser", DbType.Int32, model.CreateUser);
            db.AddInParameter(dbCommand, "@LastModifyTime", DbType.DateTime, model.LastModifyTime);
            db.AddInParameter(dbCommand, "@LastModifyUser", DbType.Int32, model.LastModifyUser);

            int count = db.ExecuteNonQuery(dbCommand);

            if (count > 0)
                return (int)dbCommand.Parameters["@Id"].Value;
            return -1;
        }

        /// <summary>
        /// 修改pmnewsShows(新闻)
        /// </summary>
        /// <param name="model">NewsShowsModel实体</param>
        /// <returns></returns>
        public static bool Update(NewsShowsModel model)
        {
            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand = db.GetSqlStringCommand(PM_NEWSSHOWS_UPDATE);

            db.AddInParameter(dbCommand, "@Id", DbType.Int32, model.Id);
            db.AddInParameter(dbCommand, "@TypeId", DbType.Int32, model.TypeId);
            db.AddInParameter(dbCommand, "@Keyword", DbType.String, model.Keyword);
            db.AddInParameter(dbCommand, "@NewsTitle", DbType.String, model.NewsTitle);
            db.AddInParameter(dbCommand, "@NewsContent", DbType.String, model.NewsContent);
            db.AddInParameter(dbCommand, "@NewsInTime", DbType.DateTime, model.NewsInTime);
            db.AddInParameter(dbCommand, "@NewsClick", DbType.Int32, model.NewsClick);
            db.AddInParameter(dbCommand, "@NewsTag", DbType.String, model.NewsTag);
            db.AddInParameter(dbCommand, "@NewsState", DbType.Boolean, model.NewsState);
            db.AddInParameter(dbCommand, "@NewsRemark", DbType.String, model.NewsRemark);
            //db.AddInParameter(dbCommand, "@CreateTime", DbType.DateTime, model.CreateTime);
            //db.AddInParameter(dbCommand, "@CreateUser", DbType.Int32, model.CreateUser);
            db.AddInParameter(dbCommand, "@LastModifyTime", DbType.DateTime, model.LastModifyTime);
            db.AddInParameter(dbCommand, "@LastModifyUser", DbType.Int32, model.LastModifyUser);

            return db.ExecuteNonQuery(dbCommand) > 0;
        }

        /// <summary>
        /// 删除 pmnewsShows(新闻)
        /// </summary>
        /// <param name="Id">model主键</param>
        /// <returns></returns>
        public static bool Delete(Int32 Id)
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetSqlStringCommand(PM_NEWSSHOWS_DELETE);
            db.AddInParameter(dbCommand, "@Id", DbType.Int32, Id);
            return db.ExecuteNonQuery(dbCommand) > 0;
        }

 
    }
}
